<?
/**
 * This file contains functions accessible for all users of the ticket system
 * 
 * @package TicketsResellersPanel
 */

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns an escaped string
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	string	$str	Unescaped string
 *
 * @return	string			Escaped string
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function escape_string($str)
{
	if (!get_magic_quotes_gpc())
		return addslashes($str); 
	else
		return $str;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns the flag of the ticket system mode 
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @return	int		Mode (0 - normal, 1 - test mode)
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_test_mode()
{
	$sql = "SELECT val1 FROM options WHERE name = 'test_mode'";
	if($GLOBALS['ticket_db']->query($sql))
	{
		$test_mode = $GLOBALS['ticket_db']->getValue();
		return $test_mode;
	}
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns the default email for every group
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 *
 * @return	array		Group emails ('admin' => 'admins@bastun.net')
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_group_mails()
{
	$sql = "SELECT val1, val2 FROM options WHERE name='group_mails'";
	if($GLOBALS['ticket_db']->query($sql))
	{
		$group_mails = $GLOBALS['ticket_db']->getAll();
		foreach($group_mails as $key=>$value)
			$mails[$value['val1']] = $value['val2'];
		return $mails;
	}else{
		$GLOBALS['statusMsg'] = 'Cannot get group mails.';
		return false;
	}		
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns links for the mail templates
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @return	array		Array with links
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_links()
{
	$sql = "SELECT val1, val2 FROM options WHERE name='links'";
	if($GLOBALS['ticket_db']->query($sql))
	{
		$links = $GLOBALS['ticket_db']->getAll();
		foreach($links as $key=>$value)
			$ret[$value['val1']] = $value['val2'];
		return $ret;
	}else{
		$GLOBALS['statusMsg'] = 'Cannot get links.';
		return false;
	}	
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns ftp upload settings
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @return	array		Array with ftp settings host,user,pass
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_upload_settings()
{	
	$sql = "SELECT val1, val2 FROM options WHERE name='upload'";
	if($GLOBALS['ticket_db']->query($sql))
	{
		$links = $GLOBALS['ticket_db']->getAll();
		foreach($links as $key=>$value)
			$ret[$value['val1']] = $value['val2'];
		return $ret;
	}else{
		$GLOBALS['statusMsg'] = 'Cannot get upload settings.';
		return false;
	}	
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns a formated date ('2006-05-04 15:14:13')
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	string	$format		Format of the returned date: d-m-Y H:i:s
 * @param	string	$timestamp	MySQL timestamp: 20060504151413
 *
 * @return	string				Formated date
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_mysql_date($format, $timestamp)
{
	if(strlen($timestamp) > 14) return $timestamp;
	
	if($timestamp)
	{
		$yearBig = substr($timestamp, 0, 4);
		$yearLittle = substr($timestamp, 2, 2);
		$mount = substr($timestamp, 4, 2);
		$day = substr($timestamp, 6, 2);
		$hours = substr($timestamp, 8, 2);
		$minutes = substr($timestamp, 10, 2);
		$seconds = substr($timestamp, 12, 2);
		$format = str_replace( 'Y', $yearBig, $format);
		$format = str_replace( 'y', $yearLittle, $format);
		$format = str_replace( 'm', $mount, $format);
		$format = str_replace( 'd', $day, $format);
		$format = str_replace( 'H', $hours, $format);
		$format = str_replace( 'i', $minutes, $format);
		$format = str_replace( 's', $seconds, $format);
		return $format;
	}else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns a MySQL timestamp ('20060504151413')
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	string	$timestamp	Unix timepstamp (optional)
 *
 * @return	string				MySQL timestamp: 20060504151413
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function set_mysql_date($timestamp = ''){
	if($timestamp) return date('YmdHis', $timestamp);
	else return date('YmdHis');
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns an array of problems
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @return	array		Array of problems
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_problems()
{
	$stmtProblems = 'SELECT id, problem
					 FROM problems';
	if ($GLOBALS['ticket_db']->query($stmtProblems))
		if($problemsArr = $GLOBALS['ticket_db']->getAll())
			return $problemsArr;
		else
			return false;
	else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns the email of the reseller
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @return	string		Email
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_reseller_mail()
{
	$sql = "SELECT email FROM resellers WHERE name='".$GLOBALS['userName']."'";
	if($GLOBALS['master_db']->query($sql))
	{
		$email = $GLOBALS['master_db']->getValue();
		return $email;
	}else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will send the selected attachment to the browser for download
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$cid			Comment id
 * @param	string	$filename	Filename
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function download_attachment($cid, $filename)
{
	$cid = (int)$cid;
	
	$uploadArr = get_upload_settings();
	
	$conn_id = ftp_connect($uploadArr['host']); 

	$login_result = ftp_login($conn_id, $uploadArr['user'], $uploadArr['pass']); 
	if ((!$conn_id) || (!$login_result)) 
	{
		echo "Download failed! Please contact the support team!";
		exit;
	}
	if(ftp_get($conn_id, 'tickets/tmp/download.tmp', $cid.'_'.$filename, FTP_BINARY))
	{
		ftp_close($conn_id);
		header("Content-type: application/octet-stream");
		header("Content-Disposition: attachment; filename=".rawurlencode($filename));
		readfile('tickets/tmp/download.tmp');
		exit;
	}else ftp_close($conn_id); 
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will change the status of the ticket (open/closed/closed-forever)
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid			Ticket id
 * @param	int	$status			New status flag
 *
 * @return	bool				True on success
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function make_status($tid, $status)
{
	$tid = (int)$tid;
	$status = (int)$status;
	
	$stmtGet = "SELECT status, type, assigned_group
				FROM tickets
				WHERE cusername = '".$GLOBALS['userName']."' and type = 3 and id = ".$tid;
	
	if ($GLOBALS['ticket_db']->query($stmtGet))
		$ticket = $GLOBALS['ticket_db']->getNext();
	else
		return false;
	
	if($ticket['assigned_group'] != 'resellers' and $ticket['type'] == 0) return false;
	
	if($ticket['status'] != 2)
	{		
		$stmtUpd = "UPDATE tickets SET status = ".$status.", close_date = now()";
		if(!$status) $stmtUpd .= ", vote1 = '', vote2 = '', vote3 = '', vote4 = '', vote_dsc = ''";
		$stmtUpd .= ' WHERE cusername = "'.$GLOBALS['userName'].'" and id = '.$tid;
		
		
		if ($GLOBALS['ticket_db']->query($stmtUpd))
		{
			if(!$status)
			{
				if($ticket['type'] == 3)
				{
					send_mail($tid, 'tss_client_reopen_ticket');
					send_mail($tid, 'tsr_reseller_reopen_ticket');
				}else{
					send_mail($tid, 'tsr_client_reopen_ticket');
					send_mail($tid, 'tsc_client_reopen_ticket');
				}
			}else{
				if($ticket['type'] == 3)
				{
					send_mail($tid, 'tss_client_close_ticket');
					send_mail($tid, 'tsr_reseller_close_ticket');
				}else{
					send_mail($tid, 'tsr_client_close_ticket');
					send_mail($tid, 'tsc_client_close_ticket');
				}
			}
			return true;
		}
	}else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will change the status of a client's ticket (some client of the reseller) (open/closed/closed-forever)
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid			Ticket id
 * @param	int	$status			New status flag
 *
 * @return	bool				True on success
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function make_clients_status($tid, $status)
{
	$tid = (int)$tid;
	$status = (int)$status;
	
	$stmtGet = "SELECT status
				FROM tickets
				WHERE assigned_group = 'resellers' and creseller = '".$GLOBALS['userName']."'  and id = ".$tid;
	
	if ($GLOBALS['ticket_db']->query($stmtGet))
		$ticket = $GLOBALS['ticket_db']->getNext();
	else
		return false;
	
	if($ticket['status'] != 2)
	{		
		$stmtUpd = "UPDATE tickets SET status = ".$status.", close_date = now()";
		if(!$status) $stmtUpd .= ", vote1 = '', vote2 = '', vote3 = '', vote4 = '', vote_dsc = ''";
		$stmtUpd .= " WHERE assigned_group = 'resellers' and creseller = '".$GLOBALS['userName']."' and id = ".$tid;
		
		
		if ($GLOBALS['ticket_db']->query($stmtUpd))
		{
			if(!$status)
			{
				send_mail($tid, 'tsr_client_reopen_ticket');
				send_mail($tid, 'tsc_client_reopen_ticket');
			}else{
				send_mail($tid, 'tsr_client_close_ticket');
				send_mail($tid, 'tsc_client_close_ticket');
			}
			return true;
		}
	}else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will change the owner of the ticket
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid					Ticket id
 * @param	string	$assigned_group		The new group
 * @param	string	$assigned_user		The new user
 *
 * @return	bool					True on success
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function change_owner($tid, $assigned_group, $assigned_user = '')
{
	$tid = (int)$tid;
	$assigned_group = escape_string($assigned_group);
	$assigned_user = escape_string($assigned_user);
	
	$sql = "UPDATE tickets SET assigned_group='".$assigned_group."', assigned_user='".$assigned_user."' WHERE id = ".$tid;
	
	if($GLOBALS['ticket_db']->query($sql))
	{
		if($assigned_user)
		{
			$comment = '----------- Assignment History: The ticket was assigned to "'.$assigned_user.'" from group "'.$assigned_group.'"! ------------ ';
		}else{
			$comment = '----------- Assignment History: The ticket was assigned to group "'.$assigned_group.'"! ------------ ';
		}
		
		$sql = "INSERT INTO comments(tid, from_user, from_group, to_user, to_group, cdate, visible, comment)
				VALUES('".$tid."', 'system', 'system', 'system', 'system', now(), '0', '".escape_string($comment)."')";
		$GLOBALS['ticket_db']->query($sql);
	}else{
		$GLOBALS['statusMsg'] = "DB error!";
		return false;
	}
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will add new ticket and will redirect the customer to the open tickets page
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 *
 * @return	bool					True on success
 *
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function add_ticket()
{
	$email = trim($_POST['umail']);
	$domain = trim($_POST['udomain']);
	$subject = escape_string(trim($_POST['usubject']));
	if(empty($subject)) $subject = "(no subject)";
	$description = escape_string(trim($_POST['udescription']));
	$language = $_SESSION['language'];
	$problem = $_POST['uproblem'];
	
	
	if(!validate($email, 'email')){
		$GLOBALS['statusMsg'] = "Invalid mail.";
		return false;
	}
	if(!validate($domain, 'domain')){
		$GLOBALS['statusMsg'] = "Invalid domain";
		return false;
	}
	if(strlen(trim($description)) < 3){
		$GLOBALS['statusMsg'] = "Ivalid description";
		return false;
	}
	
	$sql = "INSERT INTO tickets(language, problem, add_type, type, priority, checked, answered, needhelp, fixed, status, open_date, modified_date, cusername, cdomain, cemail, cserver, creseller, assigned_group, assigned_user, short_dsc, description)
			VALUES('".$language."', '".escape_string($problem)."', '1', '3', '0', '0', '0', '0', '0', '0', now(), now(), '".$GLOBALS['userName']."', '".escape_string($domain)."', '".escape_string($email)."', '', '".$GLOBALS['userName']."', 'support', '', '".escape_string($subject)."', '".escape_string($description)."')";
	
	
	if($GLOBALS['ticket_db']->query($sql))
	{
		$sql = "show table status like 'tickets'";
		$GLOBALS['ticket_db']->query($sql);
		$arrGetId = $GLOBALS['ticket_db']->getNext();
		$tid = $arrGetId['Auto_increment']-1;
		
		change_owner($tid, 'support');
		
		send_mail($tid, 'tsr_reseller_open_ticket');
		send_mail($tid, 'tss_client_open_ticket');
		
		$GLOBALS['statusMsg'] = 'Ticket successfully sent.';
		return true;
	}else{
		$GLOBALS['statusMsg'] = 'Cannot insert the ticket.';
		return false;
	}	
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns an array of values needed for the paging mechanism
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	string	$what		The SELECT part of the sql query
 * @param	string	$from		The FROM part of the sql query
 * @param	string	$where		The WHERE part of the sql query
 * @param	int		$perpage	The number of results per page (default = 50)
 *
 * @return	array				Array of values for current page, total pages, page shifts, etc.
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_pages($what, $from, $where, $perpage = 50)
{
	$pages = $GLOBALS['pages'];
	
	if(!$pages['count'])
	{
		if($GLOBALS['ticket_db']->query($what.$from.$where))
		{
			if(!$pages['count'] = $GLOBALS['ticket_db']->getValue())
				$pages['count'] = 0;
		}else return false;
	}
	
	if(!$pages['sel']) $pages['sel'] = 1;
	if(!$pages['shift']) $pages['shift'] = $perpage; // broia ticketi/komentari na edna stranitza
	$pages['total'] = ceil($pages['count']/$pages['shift']);
	if(($pages['start'] = $pages['sel'] - 10) < 0) $pages['start'] = 0;
	if(($pages['end'] = $pages['sel'] + 10) > $pages['total']) $pages['end'] = $pages['total'];
	$pages['limit'] = ($pages['sel'] * $pages['shift']) - $pages['shift'];
	
	return $pages;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns an array of average answer times for selected tickets
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	string	$where		The WHERE part of the sql query
 * @param	string	$between	The BETWEEN in the WHERE part ()
 *
 * @return	array				Array of average ticket times
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_avg_times($where = 1, $between = '')
{
	$sql = "SELECT id, avg_sec/avg_num as avg
			FROM tickets
			".$where." ".$between."
			GROUP BY id
			ORDER BY id DESC
			".$limit;
	if($GLOBALS['ticket_db']->query($sql))
	{
		$date_secs = $GLOBALS['ticket_db']->getAll();
		if(is_array($date_secs))
		{
			for($i = 0; $i < count($date_secs); $i++){
				$avg_secs = ceil($date_secs[$i]['avg']);
				$days = floor($avg_secs/86400);
				$hours = floor(fmod($avg_secs,86400)/3600);
				$minutes = floor(fmod(fmod($avg_secs,86400),3600)/60);
				$seconds = fmod(fmod(fmod($avg_secs,86400),3600),60);
				$avg_times[$date_secs[$i]['id']] = sprintf('%02u',$days).' '.sprintf('%02u',$hours).':'.sprintf('%02u',$minutes).':'.sprintf('%02u',$seconds);
				if($avg_times[$date_secs[$i]['id']] == '00 00:00:00') $avg_times[$date_secs[$i]['id']] = '|--| |--:--:--|';
			}
			return $avg_times;
		}else return false;
	}else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns the list of tickets by given type
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	string	$type	Ticket type (open, closed, noanswered, etc.)
 *
 * @return	array			Ticket list
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_ticket_list($type)
{
	$what = "SELECT count(*) as cnt";
	$from = " FROM tickets";
	if($type == 'my_open')
		$where = " WHERE tickets.type = 3 and cusername = '".$GLOBALS['userName']."' and creseller = '".$GLOBALS['userName']."' and tickets.status = 0";
	elseif($type == 'my_closed')
		$where = " WHERE tickets.type = 3 and cusername = '".$GLOBALS['userName']."' and creseller = '".$GLOBALS['userName']."' and tickets.status != 0";
	elseif($type == 'my_total')
		$where = " WHERE tickets.type = 3 and cusername = '".$GLOBALS['userName']."' and creseller = '".$GLOBALS['userName']."'";
	elseif($type == 'my_clients_open')
		$where = " WHERE tickets.type = 0 and assigned_group = 'resellers' and creseller = '".$GLOBALS['userName']."' and tickets.status = 0";
	elseif($type == 'my_clients_closed')
		$where = " WHERE tickets.type = 0 and assigned_group = 'resellers' and creseller = '".$GLOBALS['userName']."' and tickets.status != 0";
	elseif($type == 'my_clients_total')
		$where = " WHERE tickets.type = 0 and assigned_group = 'resellers' and creseller = '".$GLOBALS['userName']."'";
	elseif($type == 'my_clients_noanswered')
		$where = " WHERE tickets.type = 0 and assigned_group = 'resellers' and creseller = '".$GLOBALS['userName']."' and tickets.status = 0 and tickets.answered = 0";
	elseif($type == 'rp_support_noanswered')
		$where = " WHERE tickets.type = 0 and creseller = '".$GLOBALS['userName']."' and tickets.status = 0 and needhelp=2 and assigned_group !='resellers'";
	else
		$where = " WHERE 0";
	/*
	$pages = get_pages($what, $from, $where);
	$GLOBALS['pages'] = $pages;
	
	$limit =  ' LIMIT '.$pages['limit'].','.$pages['shift'];
	
	$GLOBALS['pages'] = $pages;
	*/
	
	$sql = "SELECT * ".$from.$where." ORDER BY id desc".$limit;
	if($GLOBALS['ticket_db']->query($sql))
	{
		$arr = $GLOBALS['ticket_db']->getAll();
		if(count($arr) > 1) $between = " and tickets.id between ".$arr[count($arr)-1]['id']." and ".$arr[0]['id'];
		if($GLOBALS['avg_times'])
		{
			$new_avg = get_avg_times($where, $between);
			foreach($new_avg as $key=>$val)
				$GLOBALS['avg_times'][$key] = $val;
		}else $GLOBALS['avg_times'] = get_avg_times($where, $between);
		return $arr;
	}else return false;	
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns the information for the selected ticket
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid			Ticket id
 *
 * @return	array				Ticket information
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_ticket_info($tid)
{
	$tid = (int)$tid;
	
	$sql = "SELECT *
			FROM tickets
			WHERE id = ".$tid;
	if($GLOBALS['ticket_db']->query($sql))
	{
		$ticket = $GLOBALS['ticket_db']->getNext();
	}else return false;	
	
	$ticket['open_date'] = get_mysql_date('Y-m-d H:i:s', $ticket['open_date']);
	$ticket['close_date'] = get_mysql_date('Y-m-d H:i:s', $ticket['close_date']);
	$ticket['modified_date'] = get_mysql_date('Y-m-d H:i:s', $ticket['modified_date']);
	
	return $ticket;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This returns the comments for the selected ticket
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid			Ticket id
 *
 * @return	array				Comments
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function get_ticket_comments($tid)
{
	$tid = (int)$tid;
	
	$what = "SELECT count(*) as cnt";
	$from = " FROM comments";
	$where = " WHERE tid = ".$tid." and (visible = 1 or from_group='resellers' or to_group='resellers') and from_user != 'system'";
	
	$pages = get_pages($what, $from, $where, 10);
	$GLOBALS['pages'] = $pages;
	
	$limit =  ' LIMIT '.$pages['limit'].','.$pages['shift'];
	
	$GLOBALS['pages'] = $pages;
	
	$sql = "SELECT * ".$from.$where." ORDER BY id desc".$limit;
	if($GLOBALS['ticket_db']->query($sql))
	{
		$commentsArr = $GLOBALS['ticket_db']->getAll();
		
		return $commentsArr;
	}else return false;	
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will insert comment for the given ticket
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid		Ticket id
 *
 * @return	bool			True on success
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function insert_comment($tid)
{
	$tid = (int)$tid;
	
	if(trim($_POST['tcomment']))
	{
		$sql = "SELECT assigned_user, assigned_group
				FROM tickets
				WHERE id = ".$tid;
		if($GLOBALS['ticket_db']->query($sql))
		{
			$ticket = $GLOBALS['ticket_db']->getNext();
		}else return false;	
		
		if(!$_FILES['attachment']['error'])
		{
			// --- get the next autoincrement index
			$sql = "show table status like 'comments' ";
			if($GLOBALS['ticket_db']->query($sql))
			{
				$arrGetId = $GLOBALS['ticket_db']->getNext();
				$lastId = $arrGetId['Auto_increment'];
			}else return false;	
			
			$uploadArr = get_upload_settings();
			if(!move_uploaded_file($_FILES['attachment']['tmp_name'], 'tickets/tmp/upload.tmp')) $GLOBALS['statusMsg'] = 'Impossible file upload';
			
			$conn_id = ftp_connect($uploadArr['host']); 

			$login_result = ftp_login($conn_id, $uploadArr['user'], $uploadArr['pass']); 
			if ((!$conn_id) || (!$login_result)) 
			{
				$GLOBALS['statusMsg'] = 'Impossible file upload';
			}else{
				if(!ftp_put($conn_id, $lastId."_".$_FILES['attachment']['name'], 'tickets/tmp/upload.tmp', FTP_BINARY))
				{
					$GLOBALS['statusMsg'] = 'Impossible file upload';
				}else{
					$file_success_name = $_FILES['attachment']['name'];
					$file_success_size = $_FILES['attachment']['size'];
				}
				ftp_close($conn_id);
			}
		}elseif($_FILES['attachment']['error'] != 4) $GLOBALS['statusMsg'] = 'Impossible file upload';

		
		$sql = "INSERT INTO comments(tid, cdate, to_user, to_group, from_user, from_group, comment, attachment, attachment_size)
				VALUES('".$tid."',now(),'".$ticket['assigned_user']."','".$ticket['assigned_group']."','".$GLOBALS['userName']."','resellers','".escape_string($_POST['tcomment'])."','".$file_success_name."','".$file_success_size."')";
		
		if($GLOBALS['ticket_db']->query($sql))
		{
			$sql = "UPDATE tickets SET answered=0, checked=0, checked_by='' WHERE id=".$tid." and cusername='".$GLOBALS['userName']."'";
			if($GLOBALS['ticket_db']->query($sql))
			{
				send_mail($tid, 'tss_client_add_comment', $_POST['tcomment']);
				return true;
			}else return false;
		}else return false;
	}
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will rate the support comment
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$cid			Comment id
 *
 * @return	bool				True on success
 * 
 * @since    10-05-2006
 * @version  1.0
 *
 */
function rate_comment($cid)
{
	$cid = (int)$cid;
	$vote1 = (int)$_POST['vote1'];
	$vote2 = (int)$_POST['vote2'];
	$vote3 = (int)$_POST['vote3'];
	$vote4 = (int)$_POST['vote4'];
	
	$sql = "UPDATE comments SET vote1=".$vote1.", vote2=".$vote2.", vote3=".$vote3.", vote4=".$vote4." WHERE id = ".$cid;
			
	if($GLOBALS['ticket_db']->query($sql)) return true;
	else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will insert comment for the given clients ticket
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid		Ticket id
 *
 * @return	bool			True on success
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function insert_clients_comment($tid)
{
	$tid = (int)$tid;
	
	if(trim($_POST['tcomment']))
	{		
		if(!$_FILES['attachment']['error'])
		{
			// --- get the next autoincrement index
			$sql = "show table status like 'comments' ";
			if($GLOBALS['ticket_db']->query($sql))
			{
				$arrGetId = $GLOBALS['ticket_db']->getNext();
				$lastId = $arrGetId['Auto_increment'];
			}else return false;	
			
			$userfile = $uploadTmpDir."/".$lastId."_".$_FILES['attachment']['name'];
			if(!move_uploaded_file($_FILES['attachment']['tmp_name'], $userfile)) $GLOBALS['statusMsg'] = 'Impossible file upload';
			
			$conn_id = ftp_connect($GLOBALS['uploadFTP']); 

			$login_result = ftp_login($conn_id, $GLOBALS['uploadUser'], $GLOBALS['uploadPass']); 
			if ((!$conn_id) || (!$login_result)) 
			{
				$GLOBALS['statusMsg'] = 'Impossible file upload';
			}else{
				if(!ftp_put($conn_id, $lastId."_".$_FILES['attachment']['name'], $userfile, FTP_BINARY))
				{
					$GLOBALS['statusMsg'] = 'Impossible file upload';
				}else{
					$file_success_name = $_FILES['attachment']['name'];
					$file_success_size = $_FILES['attachment']['size'];
				}
				ftp_close($conn_id);
			}
		}elseif($_FILES['attachment']['error'] != 4) $GLOBALS['statusMsg'] = 'Impossible file upload';

		$to_group = escape_string($_POST['to_group']);
		if($to_group == 'support') $visible = 0;
		else $visible = 1;
		
		$sql = "INSERT INTO comments(tid, cdate, to_user, to_group, visible, from_user, from_group, comment, attachment, attachment_size)
				VALUES('".$tid."',now(),'','".$to_group."', ".$visible.",'".$GLOBALS['userName']."','resellers','".escape_string($_POST['tcomment'])."','".$file_success_name."','".$file_success_size."')";
		
		if($GLOBALS['ticket_db']->query($sql))
		{
			if($to_group == 'support')
			{
				$sql = "UPDATE tickets SET checked=0, checked_by='', needhelp = 1, needhelp_from_group = 'support' WHERE id=".$tid." and creseller='".$GLOBALS['userName']."'";
				if($GLOBALS['ticket_db']->query($sql))
				{
					send_mail($tid, 'tss_client_add_comment', $_POST['tcomment'], 'support');
					return true;
				}else return false;
			}else{
				$sql = "show table status like 'comments' ";
				if($GLOBALS['ticket_db']->query($sql))
				{
					$arrGetId = $GLOBALS['ticket_db']->getNext();
					$lastId = $arrGetId['Auto_increment'];
				}else return false;
				
				set_avg_time($tid, $lastId-1);
				$sql = "UPDATE tickets SET answered=1, checked=0, checked_by='' WHERE id=".$tid." and creseller='".$GLOBALS['userName']."' and assigned_group='resellers'";
				if($GLOBALS['ticket_db']->query($sql))
				{
					send_mail($tid, 'tsc_support_add_comment', $_POST['tcomment']);
					return true;
				}else return false;
			}
		}else return false;
	}
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This sets the average answer time of the ticket supported by the reseller
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid		Ticket id
 * @param	int	$cid		Last comment id
 *
 * @return	int				Number of seconds
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function set_avg_time($tid, $cid)
{
	$tid = (int)$tid;
	$cid = (int)$cid;

	$sql = "SELECT unix_timestamp(cdate) as cdate
			FROM comments
			WHERE tid=".$tid." and from_group='Clients'
			ORDER BY id DESC
			LIMIT 1";
	if(!$GLOBALS['ticket_db']->query($sql))
	{
		return false;
	}
	$cdate_question = $GLOBALS['ticket_db']->getValue();
	
	$sql = "SELECT id
			FROM comments
			WHERE tid=".$tid." and id !='".$cid."' and to_group='Clients' and unix_timestamp(cdate) > '".($cdate_question/1)."'
			ORDER BY id DESC
			LIMIT 1";
	$GLOBALS['ticket_db']->query($sql);
	$existing_id = $GLOBALS['ticket_db']->getValue();
	if($existing_id) return false;
	
	$sql = "SELECT unix_timestamp(cdate) as cdate
			FROM comments
			WHERE id=".$cid." and to_group='Clients'
			ORDER BY id DESC
			LIMIT 1";
	if(!$GLOBALS['ticket_db']->query($sql))
	{
		return false;
	}
	$cdate_answer = $GLOBALS['ticket_db']->getValue();
	
	if(empty($cdate_question))
	{
		$sql = "SELECT unix_timestamp(open_date) as cdate
				FROM tickets
				WHERE id=".$tid;
		if(!$GLOBALS['ticket_db']->query($sql))
		{
			return false;
		}
		$cdate_question = $GLOBALS['ticket_db']->getValue();
	}
	
	if($cdate_answer < $cdate_question){
		return false;
	}
	
	$seconds = $cdate_answer - $cdate_question;

	$sql = "SELECT avg_sec as seconds, avg_num as num FROM tickets WHERE id=".$tid;
	if(!$GLOBALS['ticket_db']->query($sql))
	{
		return false;
	}
	if($avg = $GLOBALS['ticket_db']->getNext())
	{
		$sql = "UPDATE tickets SET avg_sec='".($avg['seconds']+$seconds)."', avg_num=".($avg['num']+1)." WHERE id=".$tid;
		if(!$GLOBALS['ticket_db']->query($sql))
		{
			return false;
		}else{
			return $seconds;
		}
	}else{
		return false;
	}

}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will check if the ticket is already rated by the customer
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid			Ticket id
 *
 * @return	bool				True if ticket is already rated
 * 
 * @since    10-05-2006
 * @version  1.0
 *
 */
function is_rated($tid)
{
	$tid = (int)$tid;
	
	$sql = "SELECT status, vote1 FROM tickets WHERE id=".$tid." and cusername='".$GLOBALS['userName']."'";
	if($GLOBALS['ticket_db']->query($sql))
	{
		$ticket = $GLOBALS['ticket_db']->getNext();
		if(!$ticket['status'] and $ticket['vote1']) return true;
		else return false; // vryshta false, t.eticketa e zatvoren i za nego ne e glasuvano
	}else return true;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will rate the ticket
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid			Ticket id
 *
 * @return	bool				True on success
 * 
 * @since    10-05-2006
 * @version  1.0
 *
 */
function rate_ticket($tid)
{
	$tid = (int)$tid;
	$vote1 = (int)$_POST['vote1'];
	$vote2 = (int)$_POST['vote2'];
	$vote3 = (int)$_POST['vote3'];
	$vote4 = (int)$_POST['vote4'];
	$votecomment = escape_string($_POST['votecomment']);
	
	$sql = "UPDATE tickets SET vote1=".$vote1.", vote2=".$vote2.", vote3=".$vote3.", vote4=".$vote4.", vote_dsc='".$votecomment."'
			WHERE id = ".$tid." and cusername = '".$GLOBALS['userName']."'";

	if($GLOBALS['ticket_db']->query($sql)) return true;
	else return false;
}

//////////////////////////////////////////////////////////////////////////////////////////////////
/**
 * This will send mail for every important action
 *
 * @access	public
 * @author	ogi <ogi@bastun.net>
 *
 * @param	int	$tid				Ticket id
 * @param	string	$template		Mail template to use
 * @param	string	$comment		Comment is used in some of the mail templates
 * @param	string	$to_group		Group to send mail to
 * @param	string	$to_user		User to send mail to
 *
 * @return	bool			True on success
 *
 * @since    10-05-2006
 * @version  1.0
 *
 */
function send_mail($tid, $template, $comment = '', $to_group = '', $to_user = '')
{//return false;
	$tid = (int)$tid;
	
	if(empty($tid) or empty($template)) return false;
	
	$mailcat = explode('_', $template);
	
	$sql = "SELECT * FROM tickets WHERE id=".$tid;
	
	if(!$GLOBALS['ticket_db']->query($sql))
	{
		return false;
	}
	if(!$ticket = $GLOBALS['ticket_db']->getNext())
	{
		return false;
	}
	
	$mailsArr = get_group_mails();
	
	$linksArr = get_links();
	
	$from = $mailsArr['from'];
	
	if($mailcat[0] == 'tsc')
	{
		$to = $ticket['cemail'];
		
		$link = preg_replace('/%NUM%/', $tid, $linksArr['clients']);
		$link = preg_replace('/%SERVER%/', $ticket['cserver'], $link);
		
		$vote_link = preg_replace('/%NUM%/', $tid, $linksArr['clients_vote']);
		$vote_link = preg_replace('/%SERVER%/', $ticket['cserver'], $vote_link);
		
		$list_open_link = preg_replace('/%SERVER%/', $ticket['cserver'], $linksArr['clients_list_open']);
	}elseif($mailcat[0] == 'tsr'){
		$to = get_sales_mail($GLOBALS['id']);
		unset($params);
		$params[sid] = $GLOBALS[sid];
		$params[action] = 'tickets';
		if($ticket['assigned_group'] == 'resellers') $params[option] = 'open_my_clients_ticket';
		else $params[option] = 'open_my_ticket';
		$params[tid] = $tid;
		$url = "/?".encode_params($params);
		
		$link = preg_replace('/%QUERY%/', $url, $linksArr['resellers']);	
	}elseif($mailcat[0] == 'tss'){
		if($ticket['type'] == 3) $to = $mailsArr[$ticket['assigned_group']];
		else $to = $mailsArr[$to_group];
		
		if($ticket['type'] == 3 and $ticket['assigned_group'] == 'support') $to = $mailsArr['support_resellers'];
		
		$link = preg_replace('/%NUM%/', $tid, $linksArr['admin']);
	}else return false;
	
	if($GLOBALS['test_mode']) $to = $mailsArr['test'];
	
	$mail = new SendMail($GLOBALS['master_db'], '', $to);
	$mailData = array(
		'NUM' => $tid,
		'TYPE' => $ticket['type'],
		'USERNAME' => $ticket['cusername'],
		'RESELLER' => $GLOBALS['userName'],
		'EMAIL' => $ticket['cemail'],
		'DOMAIN' => $ticket['cdomain'],
		'SERVER' => $ticket['cserver'],
		'PROBLEM' => $ticket['problem'],
		'SHORT_DSC' => $ticket['short_dsc'],
		'DESCRIPTION' => $ticket['description'],
		'COMMENT' => $comment,
		'ASSIGNED_GROUP' => $ticket['assigned_group'],
		'ASSIGNED_USER' => $ticket['assigned_user'],
		'LINK' => $link,
		'VOTE_LINK' => $vote_link,
		'LIST_OPEN_LINK' => $list_open_link
	);
	
	if($mail->Send($to, '', '', $mailData, $template, 'default', 0, array(), $from, $from)) 
	{
		 return true;
	}else{
		$GLOBALS['statusMsg'] = 'Cannot send mail.';
		 return false;
	}

	return false;
}

?>
